package org.example.TOOL.Start;

import java.sql.*;

public class pd_zyxxhzysjb1 {


    public static String tableName = "pd_zyxxhzysjb";


    private static boolean tableExists(Connection conn, String tableName) throws SQLException {

        System.out.println("正在验证 " + tableName + " 是否存在");
        String sql = "SELECT * FROM information_schema.tables WHERE table_schema = '" + Config.database + "'  AND table_name = '" + tableName + "'; ";
        try (Statement stmt = conn.createStatement()) {

            ResultSet res = stmt.executeQuery(sql);
            ResultSetMetaData rsmd = res.getMetaData();
            int columnCount = rsmd.getColumnCount();
            int rowCount = 1;
            while (res.next()) {
                if (res.getString("TABLE_NAME").equals(tableName)) {
                    return true;
                }
//                for (int i = 1; i <= columnCount; i++) {
//                    String ss = rsmd.getColumnName(i) + " : " + res.getString(i) + " ,";
//                    rowCount++;
//                    System.out.println(rowCount + " : " + ss);
//                }
            }
            return false;
        } catch (SQLException e) {
            System.out.println("查询失败");
            System.out.println(e.getMessage());
            return false;
        }
    }

    public static String careteSql = "CREATE TABLE `" + tableName + "` ( " + "  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',\n" + "  `year` int(4) DEFAULT NULL COMMENT '年份',\n" + "  `code` varchar(255) DEFAULT NULL COMMENT '学校代码',\n" + "  `name` varchar(255) DEFAULT NULL COMMENT '学校名称',\n" + "  `zydm` varchar(255) DEFAULT NULL COMMENT '专业代码',\n" + "  `zymc` varchar(255) DEFAULT NULL COMMENT '专业名称',\n" + "  `xnzydm` varchar(255) DEFAULT NULL COMMENT '校内专业代码',\n" + "  `xnzymc` varchar(255) DEFAULT NULL COMMENT '校内专业名称',\n" + "  `ssdwmc` varchar(255) DEFAULT NULL COMMENT '所属单位名称',\n" + "  `ssdwh` varchar(255) DEFAULT NULL COMMENT '所属单位号',\n" + "  `xz` varchar(255) DEFAULT NULL COMMENT '学制',\n" + "  `syxwml` varchar(255) DEFAULT NULL COMMENT '授予学位门类',\n" + "  `zszt` varchar(255) DEFAULT NULL COMMENT '招生状态',\n" + "  PRIMARY KEY (`id`) USING BTREE,\n" + "  KEY `xnzydm` (`xnzydm`),\n" + "  KEY `xnzymc` (`xnzymc`),\n" + "  KEY `zymc` (`zymc`),\n" + "  KEY `zydm` (`zydm`),\n" + "  KEY `name` (`name`),\n" + "  KEY `idx_zy` (`xnzymc`,`xnzydm`),\n" + "  KEY `year` (`year`),\n" + "  KEY `code` (`code`),\n" + "  KEY `idx_zy_year_code_xnzydm` (`year`,`code`,`xnzydm`)\n" + ") ENGINE=InnoDB AUTO_INCREMENT=28369 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='准备数据-各个学校和校内专业信息表';";

    public static void createTable(Connection conn, String tableName, String sql) {
        try (conn) {
            // 1. 判断表是否存在
            if (!tableExists(conn, tableName)) {
                // 执行建表SQL
                try (Statement terminal = conn.createStatement()) {
                    terminal.execute(sql);
                    System.out.println("表创建成功:" + tableName);
                    terminal.close();
                } catch (SQLException e) {

                    System.out.println("建表SQL执行失败:" + tableName);
                    e.printStackTrace();
                }
            } else {
                System.out.println("表已存在:" + tableName);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public static String yuanTableName = "base_zyjbqk";
    static String selectSql = "SELECT id, `year`, `code`, `name`, zymc, zydm,xnzydm, xnzymc,  ssdwmc, ssdwh, xz, syxwml, zszt  FROM " + yuanTableName + "   WHERE `year` = " + Config.year + "  AND deleted_flag = 0  GROUP BY  `code`,  xnzydm";

    //查询数据
    public static void selectData(Connection conn, String tableName, String sql) {

        try (conn) {

            Statement terminal = conn.createStatement();
            ResultSet res = terminal.executeQuery(sql);
            ResultSetMetaData dat = res.getMetaData();
            int columnCount = dat.getColumnCount();
            String[] columnsName = new String[columnCount];
            String AS = "";
            for (int i = 1; i <= columnCount; i++) {
                columnsName[i - 1] = dat.getColumnName(i);
                AS += columnsName[i - 1] + ", ";
            }
            System.out.println(AS + columnCount);

            int row = 0;
            String[] columns = {"id", "year", "code", "name", "zydm", "zymc", "xnzydm", "xnzymc", "ssdwh", "ssdwmc", "xz", "syxwml", "zszt"};

            while (res.next()) {
                StringBuilder ss = new StringBuilder();
                for (int i = 1; i <= columnCount; i++) {
                    ss.append(res.getString(columnsName[i - 1])).append(",");
                }
                row++;
                System.out.println(row + " : " + ss);  //按序号查找，序号从1开始，也可以按字段名查找
            }
            System.out.println(tableName + "表查询 共有 " + row + " 个");

            insertData(conn, res);
            terminal.close();
        } catch (SQLException e) {
            System.out.println("数据库连接有错");
            e.printStackTrace();
        }

    }

    //插入数据
    public static void insertData(Connection conn, ResultSet res) throws SQLException {

        String[] columns = {"year", "code", "name", "zydm", "zymc", "xnzydm", "xnzymc", "ssdwh", "ssdwmc", "xz", "syxwml", "zszt"};
        String sql = "INSERT INTO " + tableName + " ( year ,  code ,  name ,  zydm ,  zymc ,  xnzydm  , xnzymc ,  ssdwh ,  ssdwmc ,  xz ,  syxwml ,  zszt ) VALUES (?, ?,?, ?,?, ?,?, ?,?, ?,?, ?)";

        System.out.println("正在插入数据：" + tableName);
        try (PreparedStatement pres = conn.prepareStatement(sql)) {
            while (res.next()) {
                for (int i = 0; i <= columns.length; i++) {
                    pres.setObject(i + 1, res.getObject(columns[i]));
                }
                pres.addBatch(); // 添加到批处理
            }
            int[] i = pres.executeBatch(); // 一次性执行
            //打印插入结果
            for (int ii:i){
                System.out.println(ii);
            }
            System.out.println("数据插入成功");
        } catch (SQLException e) {
            System.out.println("发生SQL异常");
            e.printStackTrace();
        }

    }


    //每一个表都差不多分为创建表，查询插入该表的数据，插入数据，计算该表数据，更新表格数据

    public static void main(String[] args) {
        // 创建数据库连接
        try (Connection conn = DriverManager.getConnection(Config.url, Config.user, Config.password)) {
            //System.out.println(tableExists(conn, tableName));
            // createTable(conn, tableName, careteSql);
            selectData(conn, yuanTableName, selectSql);
        } catch (SQLException e) {
            e.printStackTrace();
        }


    }
}
